USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_List]    Script Date: 02/24/2013 21:06:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
ALTER PROCEDURE [dbo].[Get_List]
	-- Add the parameters for the stored procedure here
	@menuid int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [listID]
      ,[listTitle]
      ,[listDate]
      ,[listjianjie]
      ,[MenuId]
      ,[listFrom]
      ,[listAddUser]
      ,[listpoint]
      ,[listcount]
      ,[isCanBuy]
      ,[isGift]
	FROM [List]
	WHERE 1=1 AND [MenuId]=@menuid

END
GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_List_Delete]    Script Date: 02/24/2013 21:06:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
ALTER PROCEDURE [dbo].[Get_List_Delete]
	-- Add the parameters for the stored procedure here		
		@listID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE [List] WHERE listID =@listID
END

GO


USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_List_Insert]    Script Date: 02/24/2013 21:07:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
ALTER PROCEDURE [dbo].[Get_List_Insert]
	-- Add the parameters for the stored procedure here
		@listTitle  nvarchar(500),
		@listjianjie nvarchar(500),
		@listText  text,
		@MenuId int,
		@listFrom nvarchar(500),
		@listAddUser nvarchar(20),
		@point int,
		@count int,
		@isCanBuy bit,
		@isGiftFlg bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [List]
           ([listTitle]
           ,[listDate]
           ,[listjianjie]
           ,[listText]
           ,[MenuId]
           ,[listFrom]
           ,[listAddUser]
           ,[listpoint]
           ,[listcount]
           ,[isCanBuy]
           ,[isGift])
     VALUES
           (@listTitle
           ,GETDATE()
           ,@listjianjie
           ,@listText
           ,@MenuId
           ,@listFrom
           ,@listAddUser
           ,@point
           ,@count
           ,@isCanBuy
           ,@isGiftFlg)
END

GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_List_Update]    Script Date: 02/24/2013 21:07:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
ALTER PROCEDURE [dbo].[Get_List_Update]
	-- Add the parameters for the stored procedure here
		@listTitle  nvarchar(500),
		@listjianjie nvarchar(500),
		@listText  text,
		@MenuId int,
		@listFrom nvarchar(500),
		@listAddUser nvarchar(20),
		@listID int,
		@point int,
		@count int,
		@isCanBuy bit,
		@isGiftFlg bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE [List]
	   SET [listTitle] = @listTitle
		  ,[listDate] = GETDATE()
		  ,[listjianjie] = @listjianjie
		  ,[listText] = @listText
		  ,[MenuId] =@MenuId
		  ,[listFrom] = @listFrom
		  ,[listAddUser] =@listAddUser
		  ,[listpoint] = @point
		  ,[listcount] = @count
		  ,[isCanBuy] = @isCanBuy
		  ,[isGift] = @isGiftFlg
	 WHERE listID =@listID
END

GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ListALL]    Script Date: 02/24/2013 21:07:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013,01,11>
-- Description:	<UserList>
-- =============================================
ALTER PROCEDURE [dbo].[Get_ListALL]
	-- Add the parameters for the stored procedure here
		@listTitle  nvarchar(500) = null,
		@listjianjie nvarchar(500) = null,		
		@MenuId int = null,
		@listFrom nvarchar(500) = null
	
AS
DECLARE @SQL AS VARCHAR(2000);
set @SQL = 'SELECT [listID]
      ,[listTitle]      
      ,[listjianjie]     
      ,MenuName
      ,[listFrom]
      ,[listDate]
      ,[listAddUser]
      ,[listpoint]
      ,[listcount]
      ,[isCanBuy]
      ,[isGift]
  FROM [List] LEFT JOIN dbo.Menu 
  ON menu.[MenuId] = [List].MenuId
  WHERE 1=1'
		

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	 
	IF @listTitle is not null and @listTitle !=''
		SET @sql  = @sql + ' AND [listTitle] LIKE ''%'+@listTitle+'%''';
	IF @listjianjie is not null and @listjianjie !=''
		SET @sql  = @sql + ' AND [listjianjie] LIKE ''%'+@listjianjie+'%''';	
	IF @listFrom is not null and @listFrom !=''
		SET @sql  = @sql + ' AND [listFrom] LIKE ''%'+@listFrom+'%''';
	IF @MenuId is not null and @MenuId != -1 
		EXEC(@sql + ' AND [List].MenuId = '+@MenuId +' ORDER BY [listDate] DESC')
	ELSE
		EXEC(@sql + 'ORDER BY [listDate] DESC');
	
END

GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ListByID]    Script Date: 02/24/2013 21:07:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
ALTER PROCEDURE [dbo].[Get_ListByID]
	-- Add the parameters for the stored procedure here
	@List_ID int	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [listID]
      ,[listTitle]
      ,[listDate]
      ,[listjianjie]
      ,[listText]
      ,[MenuId]
      ,[listFrom]
      ,[listAddUser]
      ,[listpoint]
      ,[listcount]
      ,[isCanBuy]
      ,[isGift]
	FROM [List]
	WHERE 1=1 AND [listID]=@List_ID 
END

GO








